<?php

namespace App\Libraries\RDBS;

/**
 * 查询构造器
 * @package App\Libraries\RDBS
 */
class QueryBuilder extends Database {

    /**
     * 是否启用【保护标识符】
     * @var bool
     */
    var $_protectIdentifiers = true;
    /**
     * 不需要【保护标识符】的字段名列表
     * @var array
     */
    var $_reservedIdentifiers = array('*');

    /**
     * 转义字符串（Mysql的保护标识符）
     * @var string
     */
    var $_escapeChar = '`';

    /**
     * COUNT 语句
     * @var string
     */
    var $_countString = 'SELECT COUNT(*) AS ';

    #region  Query Builder variables

    /**
     * Return DELETE SQL flag
     * @var    bool
     */
    protected $_returnDeleteSql = FALSE;

    /**
     * Reset DELETE data flag
     * @var    bool
     */
    protected $_resetDeleteData = FALSE;

    /**
     * QB SELECT data
     * @var    array
     */
    protected $_select = array();

    /**
     * QB DISTINCT flag
     * @var    bool
     */
    protected $_distinct = FALSE;

    /**
     * QB FROM data
     * @var    array
     */
    protected $_from = array();

    /**
     * QB JOIN data
     * @var    array
     */
    protected $_join = array();

    /**
     * QB WHERE data
     * @var    array
     */
    protected $_where = array();

    /**
     * QB GROUP BY data
     * @var    array
     */
    protected $_groupBy = array();

    /**
     * QB HAVING data
     *
     * @var    array
     */
    protected $_having = array();

    /**
     * QB keys
     *
     * @var    array
     */
    protected $_keys = array();

    /**
     * QB LIMIT data
     *
     * @var    int
     */
    protected $_limit = FALSE;

    /**
     * QB OFFSET data
     *
     * @var    int
     */
    protected $_offset = FALSE;

    /**
     * QB ORDER BY data
     *
     * @var    array
     */
    protected $_orderBy = array();

    /**
     * QB data sets
     *
     * @var    array
     */
    protected $_set = array();

    /**
     * QB aliased tables list
     *
     * @var    array
     */
    protected $_aliasedTables = array();

    /**
     * QB WHERE group started flag
     *
     * @var    bool
     */
    protected $_whereGroupStarted = FALSE;

    /**
     * QB WHERE group count
     *
     * @var    int
     */
    protected $_whereGroupCount = 0;

    /**
     * QB No Escape data
     *
     * @var    array
     */
    protected $_noEscape = array();

    #endregion

    #region Query Builder Caching variables

    /**
     * QB Caching flag
     *
     * @var    bool
     */
    protected $_caching = FALSE;

    /**
     * QB Cache exists list
     *
     * @var    array
     */
    protected $_cacheExists = array();

    /**
     * QB Cache SELECT data
     *
     * @var    array
     */
    protected $_cacheSelect = array();

    /**
     * QB Cache FROM data
     *
     * @var    array
     */
    protected $_cacheFrom = array();

    /**
     * QB Cache JOIN data
     *
     * @var    array
     */
    protected $_cacheJoin = array();

    /**
     * QB Cache WHERE data
     *
     * @var    array
     */
    protected $_cacheWhere = array();

    /**
     * QB Cache GROUP BY data
     *
     * @var    array
     */
    protected $_cacheGroupBy = array();

    /**
     * QB Cache HAVING data
     *
     * @var    array
     */
    protected $_cacheHaving = array();

    /**
     * QB Cache ORDER BY data
     *
     * @var    array
     */
    protected $_cacheOrderBy = array();

    /**
     * QB Cache data sets
     *
     * @var    array
     */
    protected $_cacheSet = array();

    /**
     * QB Cache No Escape data
     *
     * @var    array
     */
    protected $_cacheNoEscape = array();

    #endregion

    #region Query Builder

    #region Select

    /**
     * Select 生成查询的Select部分
     * @param    string | array $select
     * @param    mixed $escape 保护标识符
     * @return    QueryBuilder
     */
    public function select($select = '*', $escape = NULL) {
        if (is_string($select)) {
            $select = explode(',', $select);
        }

        // If the escape value was not set, we will base it on the global setting
        // 如果没有设置【保护标识符】，则设置默认的全局
        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        foreach ($select as $val) {
            $val = trim($val);

            if ($val !== '') {
                $this->_select[] = $val;
                $this->_noEscape[] = $escape;

                if ($this->_caching === TRUE) {
                    $this->_cacheSelect[] = $val;
                    $this->_cacheExists[] = 'select';
                    $this->_cacheNoEscape[] = $escape;
                }
            }
        }

        return $this;
    }

    #endregion

    #region Select MAX

    /**
     * Select Max 生成查询的 Select MAX(field)部分
     *
     * @param    string $select 要查询的字段名
     * @param    string $alias 别名
     * @return    QueryBuilder
     */
    public function max($select = '', $alias = '') {
        return $this->_maxMinAvgSum($select, $alias, 'MAX');
    }

    #endregion

    #region Select MIN

    /**
     * Select Min 生成查询的 Select MIN(field)部分
     *
     * @param    string $select 要查询的字段名
     * @param    string $alias 别名
     * @return    QueryBuilder
     */
    public function min($select = '', $alias = '') {
        return $this->_maxMinAvgSum($select, $alias, 'MIN');
    }

    #endregion

    #region Select Average

    /**
     * Select Average 生成查询的 Select AVG(field)部分
     *
     * @param    string $select 要查询的字段名
     * @param    string $alias 别名
     * @return    QueryBuilder
     */
    public function avg($select = '', $alias = '') {
        return $this->_maxMinAvgSum($select, $alias, 'AVG');
    }

    #endregion

    #region Select SUM

    /**
     * Select Sum 生成查询的 Select SUM(field)部分
     *
     * @param    string $select 要查询的字段名
     * @param    string $alias 别名
     * @return    QueryBuilder
     */
    public function sum($select = '', $alias = '') {
        return $this->_maxMinAvgSum($select, $alias, 'SUM');
    }

    #endregion

    #region SELECT [MAX|MIN|AVG|SUM]()

    /**
     * SELECT [MAX|MIN|AVG|SUM]() 生成 SELECT [MAX|MIN|AVG|SUM]() 语句
     * @used-by    select_max()
     * @used-by    select_min()
     * @used-by    select_avg()
     * @used-by    select_sum()
     *
     * @param    string $select 要查询的字段名
     * @param    string $alias 别名
     * @param    string $type 类型
     * @return    CI_DB_query_builder
     * @throws \Exception
     */
    protected function _maxMinAvgSum($select = '', $alias = '', $type = 'MAX') {
        if (!is_string($select) OR $select === '') {
            throw new \Exception('无效的数据库查询');
        }

        $type = strtoupper($type);

        if (!in_array($type, array('MAX', 'MIN', 'AVG', 'SUM'))) {
            throw new \Exception("无效的数据库查询方式:{$type}");
        }

        // 如果没有设置别名，则根据表名来确定别名
        if ($alias === '') {
            $alias = $this->_createAliasFromTable(trim($select));
        }

        $sql = $type . '(' . $this->_protectIdentifiers(trim($select)) . ') AS ' . $this->_escapeIdentifiers(trim($alias));

        $this->_select[] = $sql;
        $this->_noEscape[] = NULL;

        if ($this->_caching === TRUE) {
            $this->_cacheSelect[] = $sql;
            $this->_cacheExists[] = 'select';
        }

        return $this;
    }

    #endregion

    #region DISTINCT

    /**
     * DISTINCT 设置DISTINCT标志，告诉查询字符串编译器添加 DISTINCT
     *
     * @param    bool $val
     * @return    QueryBuilder
     */
    public function distinct($val = TRUE) {
        $this->_distinct = is_bool($val) ? $val : TRUE;
        return $this;
    }

    #endregion

    #region From

    /**
     * From 生成查询的FROM部分
     *
     * @param    string | array $from 表名或者表名列表
     * @return    QueryBuilder
     */
    public function from($from) {
        foreach ((array)$from as $val) {
            if (strpos($val, ',') !== FALSE) {
                foreach (explode(',', $val) as $v) {
                    $v = trim($v);
                    // 提取任何可能存在的别名，在_protectIdentifiers方法会用到
                    $this->_trackAliases($v);

                    $this->_from[] = $v = $this->_protectIdentifiers($v, TRUE, NULL, FALSE);

                    if ($this->_caching === TRUE) {
                        $this->_cacheFrom[] = $v;
                        $this->_cacheExists[] = 'from';
                    }
                }
            } else {
                $val = trim($val);

                // 提取任何可能存在的别名，在_protectIdentifiers方法会用到
                $this->_trackAliases($val);

                $this->_from[] = $val = $this->_protectIdentifiers($val, TRUE, NULL, FALSE);

                if ($this->_caching === TRUE) {
                    $this->_cacheFrom[] = $val;
                    $this->_cacheExists[] = 'from';
                }
            }
        }

        return $this;
    }

    #endregion

    #region JOIN

    /**
     * JOIN 生成查询的JOIN部分
     *
     * @param    string JOIN 表
     * @param    string    连接条件
     * @param    string    连接类型
     * @param    string 是否需要添加保护标识符
     * @return    QueryBuilder
     */
    public function join($table, $cond, $type = '', $escape = NULL) {
        if ($type !== '') {
            $type = strtoupper(trim($type));

            if (!in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE)) {
                $type = '';
            } else {
                $type .= ' ';
            }
        }

        // 提取任何可能存在的别名，在_protectIdentifiers方法会用到
        $this->_trackAliases($table);

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        // 拆分多个条件
        if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_OFFSET_CAPTURE)) {
            $newCond = '';
            $m[0][] = array('', strlen($cond));

            for ($i = 0, $c = count($m[0]), $s = 0;
                 $i < $c;
                 $s = $m[0][$i][1] + strlen($m[0][$i][0]), $i++) {
                $temp = substr($cond, $s, ($m[0][$i][1] - $s));

                $newCond .= preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $temp, $match)
                    ? $this->_protectIdentifiers($match[1]) . $match[2] . $this->_protectIdentifiers($match[3])
                    : $temp;

                $newCond .= $m[0][$i][0];
            }

            $cond = ' ON ' . $newCond;
        } elseif ($escape === TRUE && preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $cond, $match)) {
            // 拆分查询条件，兵器设置保护标识符
            $cond = ' ON ' . $this->_protectIdentifiers($match[1]) . $match[2] . $this->_protectIdentifiers($match[3]);
        } elseif (!$this->_hasOperator($cond)) {
            $cond = ' USING (' . ($escape ? $this->_escapeIdentifiers($cond) : $cond) . ')';
        } else {
            $cond = ' ON ' . $cond;
        }

        // 是否需要为表名添加保护标识符
        if ($escape === TRUE) {
            $table = $this->_protectIdentifiers($table, TRUE, NULL, FALSE);
        }

        // Assemble the JOIN statement
        // 组装JOIN语句
        $this->_join[] = $join = $type . 'JOIN ' . $table . $cond;

        if ($this->_caching === TRUE) {
            $this->_cacheJoin[] = $join;
            $this->_cacheExists[] = 'join';
        }

        return $this;
    }

    #endregion

    #region Where

    /**
     * Where 生成查询条件的Where语句部分，多个时用AND连接
     *
     * Generates the WHERE portion of the query. Separates
     * multiple calls with AND
     *
     * @param    mixed $key 键
     * @param    mixed $value 值
     * @param    bool $escape 是否需要添加保护标识符
     * @return   QueryBuilder
     */
    public function where($key, $value = NULL, $escape = TRUE) {
        return $this->_where('_where', $key, $value, 'AND ', $escape);
    }

    #endregion

    #region OR Where
    /**
     * OR Where 生成查询条件的Where语句部分，多个时用OR连接
     *
     * @param    mixed $key 键
     * @param    mixed $value 值
     * @param    bool $escape 是否需要添加保护标识符
     * @return   QueryBuilder
     */
    public function orWhere($key, $value = NULL, $escape = TRUE) {
        return $this->_where('_where', $key, $value, 'OR ', $escape);
    }

    #endregion

    #region HERE, HAVING

    /**
     * WHERE, HAVING
     *
     *
     * @param    string $qbKey 缓存键 '_where' or '_having'
     * @param    mixed $key 键
     * @param    mixed $value 值
     * @param    mixed $type WHERE 类型 （OR 或者 AND）
     * @param    bool $escape 是否需要添加保护标识符
     * @return   QueryBuilder
     */
    protected function _where($qbKey, $key, $value = NULL, $type = 'AND ', $escape = NULL) {

        $cacheKey = ($qbKey === '_having') ? '_cacheHaving' : '_cacheWhere';

        if (!is_array($key)) {
            $key = array($key => $value);
        }

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        foreach ($key as $k => $v) {
            $prefix = (count($this->$qbKey) === 0 && count($this->$cacheKey) === 0)
                ? $this->_groupGetType('')
                : $this->_groupGetType($type);

            if ($v !== NULL) {
                if ($escape === TRUE) {
                    $v = ' ' . $this->escape($v);
                }

                if (!$this->_hasOperator($k)) {
                    $k .= ' = ';
                }
            } elseif (!$this->_hasOperator($k)) {
                // value appears not to have been set, assign the test to IS NULL
                $k .= ' IS NULL';
            } elseif (preg_match('/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i', $k, $match, PREG_OFFSET_CAPTURE)) {
                $k = substr($k, 0, $match[0][1]) . ($match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL');
            }

            $this->{$qbKey}[] = array('condition' => $prefix . $k . $v, 'escape' => $escape);
            if ($this->_caching === TRUE) {
                $this->{$cacheKey}[] = array('condition' => $prefix . $k . $v, 'escape' => $escape);
                $this->_cacheExistsxists[] = substr($qbKey, 3);
            }
        }
        return $this;
    }

    #endregion

    #region GROUP BY

    /**
     * GROUP BY 生成Group By 部分
     *
     * @param    string $by
     * @param    bool $escape
     * @return    QueryBuilder
     */
    public function groupBy($by, $escape = NULL) {
        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        if (is_string($by)) {
            $by = ($escape === TRUE)
                ? explode(',', $by)
                : array($by);
        }

        foreach ($by as $val) {
            $val = trim($val);

            if ($val !== '') {
                $val = array('field' => $val, 'escape' => $escape);

                $this->_groupBy[] = $val;
                if ($this->_caching === TRUE) {
                    $this->_cacheGroupBy[] = $val;
                    $this->_cacheExists[] = 'groupby';
                }
            }
        }

        return $this;
    }

    #endregion

    #region HAVING

    /**
     * HAVING 生成查询语句的HAVING，使用AND连接多个条件
     *
     * @param    string $key
     * @param    string $value
     * @param    bool $escape
     * @return    QueryBuilder
     */
    public function having($key, $value = NULL, $escape = NULL) {
        return $this->_where('_having', $key, $value, 'AND ', $escape);
    }

    #endregion

    #region OR HAVING

    /**
     * OR HAVING 生成查询语句的HAVING，使用OR连接多个条件
     *
     * @param    string $key
     * @param    string $value
     * @param    bool $escape
     * @return    QueryBuilder
     */
    public function orHaving($key, $value = NULL, $escape = NULL) {
        return $this->_where('_having', $key, $value, 'OR ', $escape);
    }

    #endregion

    #region ORDER BY

    /**
     * ORDER BY
     *
     * @param    string $orderBy
     * @param    string $direction ASC, DESC or RANDOM
     * @param    bool $escape
     * @return    QueryBuilder
     */
    public function orderBy($orderBy, $direction = '', $escape = NULL) {
        $direction = strtoupper(trim($direction));

        if ($direction === 'RANDOM') {
            $direction = '';
            // Do we have a seed value?
            $orderBy = ctype_digit((string)$orderBy)
                ? sprintf($this->_random_keyword[1], $orderBy)
                : $this->_random_keyword[0];
        } elseif (empty($orderBy)) {
            return $this;
        } elseif ($direction !== '') {
            $direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' ' . $direction : '';
        }

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        if ($escape === FALSE) {
            $qbOrderBy[] = array('field' => $orderBy, 'direction' => $direction, 'escape' => FALSE);
        } else {
            $qbOrderBy = array();
            foreach (explode(',', $orderBy) as $field) {
                $qbOrderBy[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))
                    ? array('field' => ltrim(substr($field, 0, $match[0][1])), 'direction' => ' ' . $match[1][0], 'escape' => TRUE)
                    : array('field' => trim($field), 'direction' => $direction, 'escape' => TRUE);
            }
        }

        $this->_orderBy = array_merge($this->_orderBy, $qbOrderBy);
        if ($this->_caching === TRUE) {
            $this->_cacheOrderBy = array_merge($this->_cacheOrderby, $qbOrderBy);
            $this->_cacheExists[] = 'orderby';
        }

        return $this;
    }

    #endregion

    #region LIMIT

    /**
     * LIMIT
     *
     * @param    int $value LIMIT value
     * @param    int $offset OFFSET value
     * @return    QueryBuilder
     */
    public function limit($value, $offset = 0) {
        is_null($value) OR $this->_limit = (int)$value;
        empty($offset) OR $this->_offset = (int)$offset;

        return $this;
    }

    #endregion

    #region 设置 OFFSET 的值

    /**
     * 设置 OFFSET 的值
     *
     * @param    int $offset OFFSET value
     * @return    QueryBuilder
     */
    public function offset($offset) {
        empty($offset) OR $this->_offset = (int)$offset;
        return $this;
    }

    #endregion

    #region 为Sql追加生成LIMIT语句

    /**
     * 生成LIMIT语句
     *
     * @param    string $sql SQL Query
     * @return    string
     */
    protected function _limit($sql) {
        return $sql . ' LIMIT ' . ($this->_offset ? $this->_offset . ', ' : '') . $this->_limit;
    }

    #endregion

    #region SET 函数

    /**
     * SET 函数
     *
     * @param    string $key
     * @param    string $value
     * @param    bool $escape 是否保护标识符
     * @return    QueryBuilder
     */
    public function set($key, $value = '', $escape = NULL) {
        $key = $this->_objectToArray($key);

        if (!is_array($key)) {
            $key = array($key => $value);
        }

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        foreach ($key as $k => $v) {
            $this->_set[$this->_protectIdentifiers($k, FALSE, $escape)] = ($escape)
                ? $this->escape($v) : $v;
        }

        return $this;
    }

    #endregion

    #region 获取SELECT查询语句

    /**
     * 获取SELECT查询语句
     *
     * @param    string $table FROM 表名
     * @param    bool $reset 是否重设
     * @return    string
     */
    public function getCompiledSelect($table = '', $reset = TRUE) {
        if ($table !== '') {
            $this->_trackAliases($table);
            $this->from($table);
        }

        $select = $this->_compileSelect();

        if ($reset === TRUE) {
            $this->_resetSelect();
        }

        return $select;
    }

    #endregion

    #region 生成一个SELECT查询语句

    /**
     * 生成一个SELECT查询语句
     *
     * @param    bool $selectOverride
     * @return    string
     */
    protected function _compileSelect($selectOverride = FALSE) {
        // 合并缓存
        $this->_mergeCache();

        // 查询的"SELECT"部分
        if ($selectOverride !== FALSE) {
            $sql = $selectOverride;
        } else {
            $sql = (!$this->_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';

            if (count($this->_select) === 0) {
                $sql .= '*';
            } else {
                // Cycle through the "select" portion of the query and prep each column name.
                // The reason we protect identifiers here rather then in the select() function
                // is because until the user calls the from() function we don't know if there are aliases
                foreach ($this->_select as $key => $val) {
                    $noEscape = isset($this->_noEscape[$key]) ? $this->_noEscape[$key] : NULL;
                    $this->_select[$key] = $this->_protectIdentifiers($val, FALSE, $noEscape);
                }

                $sql .= implode(', ', $this->_select);
            }
        }

        // 查询的"FROM"部分
        if (count($this->_from) > 0) {
            $sql .= "\nFROM " . $this->_fromTables();
        }

        // 查询的"JOIN"部分
        if (count($this->_join) > 0) {
            $sql .= "\n" . implode("\n", $this->_join);
        }

        $sql .= $this->_compileWhere('_where')
            . $this->_compileGroupBy()
            . $this->_compileWhere('_having')
            . $this->_compileOrderBy(); // ORDER BY

        // LIMIT
        if ($this->_limit) {
            return $this->_limit($sql . "\n");
        }

        return $sql;
    }

    #endregion

    #region 分解FROM 表

    /**
     * 分解FROM 表
     * 其他数据库时，可以重写该方法
     * @return    string
     */
    protected function _fromTables() {
        return implode(', ', $this->_from);
    }

    #endregion

    #region 生成 WHERE/HAVING 声明

    /**
     * 生成 WHERE/HAVING 声明
     *
     * @param    string $key '_where' or '_having'
     * @return    string    SQL statement
     */
    protected function _compileWhere($key) {
        if (count($this->$key) > 0) {
            for ($i = 0, $c = count($this->$key); $i < $c; $i++) {
                // Is this condition already compiled?
                if (is_string($this->{$key}[$i])) {
                    continue;
                } elseif ($this->{$key}[$i]['escape'] === FALSE) {
                    $this->{$key}[$i] = $this->{$key}[$i]['condition'];
                    continue;
                }

                // Split multiple conditions
                $conditions = preg_split(
                    '/(\s*AND\s+|\s*OR\s+)/i',
                    $this->{$key}[$i]['condition'],
                    -1,
                    PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
                );

                for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++) {
                    if (($op = $this->_getOperator($conditions[$ci])) === FALSE
                        OR !preg_match('/^(\(?)(.*)(' . preg_quote($op, '/') . ')\s*(.*(?<!\)))?(\)?)$/i', $conditions[$ci], $matches)
                    ) {
                        continue;
                    }

                    // $matches = array(
                    //	0 => '(test <= foo)',	/* the whole thing */
                    //	1 => '(',		/* optional */
                    //	2 => 'test',		/* the field name */
                    //	3 => ' <= ',		/* $op */
                    //	4 => 'foo',		/* optional, if $op is e.g. 'IS NULL' */
                    //	5 => ')'		/* optional */
                    // );

                    if (!empty($matches[4])) {
                        $this->_isLiteral($matches[4]) OR $matches[4] = $this->_protectIdentifiers(trim($matches[4]));
                        $matches[4] = ' ' . $matches[4];
                    }

                    $conditions[$ci] = $matches[1] . $this->_protectIdentifiers(trim($matches[2]))
                        . ' ' . trim($matches[3]) . $matches[4] . $matches[5];
                }

                $this->{$key}[$i] = implode('', $conditions);
            }

            return ($key === '_having' ? "\nHAVING " : "\nWHERE ")
                . implode("\n", $this->$key);
        }

        return '';
    }

    #endregion

    #region 生成 GROUP BY

    /**
     * 生成 GROUP BY
     * @return    string    SQL statement
     */
    protected function _compileGroupBy() {
        if (count($this->_groupBy) > 0) {
            for ($i = 0, $c = count($this->_groupBy); $i < $c; $i++) {
                // Is it already compiled?
                if (is_string($this->_groupBy[$i])) {
                    continue;
                }

                $this->_groupBy[$i] = ($this->_groupBy[$i]['escape'] === FALSE OR $this->_isLiteral($this->_groupBy[$i]['field']))
                    ? $this->_groupBy[$i]['field']
                    : $this->_protectIdentifiers($this->_groupBy[$i]['field']);
            }

            return "\nGROUP BY " . implode(', ', $this->_groupBy);
        }

        return '';
    }

    #endregion

    #region 生成 ORDER  BY

    /**
     * 生成 ORDER BY
     *
     * @return    string    SQL statement
     */
    protected function _compileOrderBy() {
        if (is_array($this->_orderBy) && count($this->_orderBy) > 0) {
            for ($i = 0, $c = count($this->_orderBy); $i < $c; $i++) {
                if ($this->_orderBy[$i]['escape'] !== FALSE && !$this->_isLiteral($this->_orderBy[$i]['field'])) {
                    $this->_orderBy[$i]['field'] = $this->_protectIdentifiers($this->_orderBy[$i]['field']);
                }

                $this->_orderBy[$i] = $this->_orderBy[$i]['field'] . $this->_orderBy[$i]['direction'];
            }

            return $this->_orderBy = "\nORDER BY " . implode(', ', $this->_orderBy);
        } elseif (is_string($this->_orderBy)) {
            return $this->_orderBy;
        }

        return '';
    }

    #endregion

    #region 根据表名来确定别名

    /**
     * 根据表名来确定别名
     *
     * @param    string $item
     * @return    string
     */
    protected function _createAliasFromTable($item) {
        if (strpos($item, '.') !== FALSE) {
            $item = explode('.', $item);
            return end($item);
        }

        return $item;
    }

    #endregion

    #region 保护标识符

    /**
     * 根据列名或表名处理保护标识符
     *
     * @access    private
     * @param    string
     * @param    bool
     * @param    mixed
     * @param    bool
     * @return    string
     */
    protected function _protectIdentifiers($item, $prefixSingle = FALSE, $_protectIdentifiers = NULL, $fieldExists = TRUE) {
        if (!is_bool($_protectIdentifiers)) {
            $_protectIdentifiers = $this->_protectIdentifiers;
        }

        if (is_array($item)) {
            $escapedArray = array();

            foreach ($item as $k => $v) {
                $escapedArray[$this->_protectIdentifiers($k)] = $this->_protectIdentifiers($v);
            }

            return $escapedArray;
        }

        // 将制表符或多个空格转换成单个空格
        $item = preg_replace('/[\t ]+/', ' ', $item);

        // 如果该项目已有别名声明，则删除它。
        // 移除右边的第一个空格
        if (strpos($item, ' ') !== FALSE) {
            $alias = strstr($item, ' ');
            $item = substr($item, 0, -strlen($alias));
        } else {
            $alias = '';
        }

        // 如果没有有括号则不处理，直接返回
        if (strpos($item, '(') !== FALSE) {
            return $item . $alias;
        }

        // 如果包含【.】，则拆分字符串，然后在正确位置插入表前缀；如果没有【.】,则跳过。
        if (strpos($item, '.') !== FALSE) {
            $parts = explode('.', $item);

            // 拆分的第一部分是否匹配之前的确定的别名
            if (in_array($parts[0], $this->_aliasedTables)) {
                if ($_protectIdentifiers === TRUE) {
                    foreach ($parts as $key => $val) {
                        if (!in_array($val, $this->_reservedIdentifiers)) {
                            $parts[$key] = $this->_escapeIdentifiers($val);
                        }
                    }

                    $item = implode('.', $parts);
                }
                return $item . $alias;
            }

            if ($_protectIdentifiers === TRUE) {
                $item = $this->_escapeIdentifiers($item);
            }

            return $item . $alias;
        }

        if ($_protectIdentifiers === TRUE AND !in_array($item, $this->_reservedIdentifiers)) {
            $item = $this->_escapeIdentifiers($item);
        }

        return $item . $alias;
    }

    #endregion

    #region 转义 SQL 的保护标识符

    /**
     * 转义 SQL 的保护标识符
     * @access    private
     * @param    string
     * @return    string
     */
    protected function _escapeIdentifiers($item) {
        if ($this->_escapeChar == '') {
            return $item;
        }

        foreach ($this->_reservedIdentifiers as $id) {
            if (strpos($item, '.' . $id) !== FALSE) {
                $str = $this->_escapeChar . str_replace('.', $this->_escapeChar . '.', $item);

                // 如果用户传递的数据已经包含【保护标识符】，则删除重复项
                return preg_replace('/[' . $this->_escapeChar . ']+/', $this->_escapeChar, $str);
            }
        }

        if (strpos($item, '.') !== FALSE) {
            $str = $this->_escapeChar . str_replace('.', $this->_escapeChar . '.' . $this->_escapeChar, $item) . $this->_escapeChar;
        } else {
            $str = $this->_escapeChar . $item . $this->_escapeChar;
        }

        // 如果用户传递的数据已经包含【保护标识符】，则删除重复项
        return preg_replace('/[' . $this->_escapeChar . ']+/', $this->_escapeChar, $str);
    }

    #endregion

    #region 提取别名

    /**
     * 提取别名，将别名添加到别名列表中
     *
     * @param    string    要检查的表
     * @return    string
     */
    protected function _trackAliases($table) {
        if (is_array($table)) {
            foreach ($table as $t) {
                $this->_trackAliases($t);
            }
            return;
        }

        // Does the string contain a comma?  If so, we need to separate
        // the string into discreet statements
        if (strpos($table, ',') !== FALSE) {
            return $this->_trackAliases(explode(',', $table));
        }

        // if a table alias is used we can recognize it by a space
        if (strpos($table, ' ') !== FALSE) {
            // if the alias is written with the AS keyword, remove it
            $table = preg_replace('/\s+AS\s+/i', ' ', $table);

            // Grab the alias
            $table = trim(strrchr($table, ' '));

            // Store the alias, if it doesn't already exist
            if (!in_array($table, $this->_aliasedTables)) {
                $this->_aliasedTables[] = $table;
            }
        }
    }

    #endregion

    #region 是否包含sql的操作符

    /**
     * 是否包含sql的操作符
     *
     * @access    protected
     * @param    string
     * @return    bool
     */
    protected function _hasOperator($str) {
        $str = trim($str);
        if (!preg_match("/(\s|<|>|!|=|is null|is not null)/i", $str)) {
            return FALSE;
        }

        return TRUE;
    }

    #endregion

    #region 获取sql的操作符

    /**
     * 获取sql的操作符
     *
     * @access    protected
     * @param    string
     * @return    bool
     */
    protected function _getOperator($str) {
        $str = trim($str);
        $matches = array();
        if (!preg_match_all("/(\s|<|>|!|=|is null|is not null)/i", $str, $matches)) {
            return FALSE;
        }

        return $matches[1][0];
    }

    #endregion

    #region 运行SELECT 语句

    /**
     * 运行SELECT 语句
     *
     * @param    string    the table
     * @param    string    the limit clause
     * @param    string    the offset clause
     * @return    \Phalcon\Db\ResultInterface
     */
    public function get($table = '', $limit = NULL, $offset = NULL) {
        if ($table !== '') {
            $this->_trackAliases($table);
            $this->from($table);
        }

        if (!empty($limit)) {
            $this->limit($limit, $offset);
        }

        $result = $this->query($this->_compileSelect());
        $this->_resetSelect();
        return $result;
    }

    #endregion

    #region 查询所有结果的总数

    /**
     * 查询所有结果的总数
     *
     * @param    string
     * @param    bool    the reset clause
     * @return    int
     */
    public function countAllResults($table = '', $reset = TRUE) {
        if ($table !== '') {
            $this->_trackAliases($table);
            $this->from($table);
        }

        $result = ($this->_distinct === TRUE)
            ? $this->fetchOne($this->_countString . $this->_protectIdentifiers('count') . "\nFROM (\n" . $this->_compileSelect() . "\n) t")
            : $this->fetchOne($this->_compileSelect($this->_countString . $this->_protectIdentifiers('count')));

        if ($reset === TRUE) {
            $this->_resetSelect();
        }

        return $result ? intval($result["count"]) : 0;
    }

    #endregion

    #region 运行SELECT 语句 ，带WHERE条件

    /**
     * 运行SELECT 语句 ，带WHERE条件
     *
     * @param    string $table
     * @param    string $where
     * @param    int $limit
     * @param    int $offset
     * @return    \Phalcon\Db\ResultInterface
     */
    public function getAsWhere($table = '', $where = NULL, $limit = NULL, $offset = NULL) {
        if ($table !== '') {
            $this->from($table);
        }

        if ($where !== NULL) {
            $this->where($where);
        }

        if (!empty($limit)) {
            $this->limit($limit, $offset);
        }

        $result = $this->query($this->_compileSelect());
        $this->_resetSelect();
        return $result;
    }

    #endregion

    #region 批量插入

    /**
     * 批量插入
     *
     * @param    string $table 要插入数据的表
     * @param    array $set 要插入的数据集
     * @param    bool $escape 是否添加保护标识符
     * @return   int | bool 成功插入数据的总数 或者 插入失败返回FALSE
     */
    public function insertBatch($table = '', $set = NULL, $escape = NULL) {
        if ($set !== NULL) {
            $this->setInsertBatch($set, '', $escape);
        }

        if (count($this->_set) === 0) {
            // No valid data array. Folds in cases where keys and values did not match up
            throw new \mysqli_sql_exception("db_must_use_set");
        }

        if ($table === '') {
            if (!isset($this->_from[0])) {
                throw new \mysqli_sql_exception("db_must_set_table");
            }

            $table = $this->_from[0];
        }

        // Batch this baby
        $affectedRows = 0;
        for ($i = 0, $total = count($this->_set); $i < $total; $i += 100) {
            $this->execute($this->_insertBatch($this->_protectIdentifiers($table, TRUE, $escape, FALSE), $this->_keys, array_slice($this->_set, $i, 100)));
            $affectedRows += $this->affectedRows();
        }

        $this->_resetWrite();
        return $affectedRows;
    }

    #endregion

    #region 生成批量插入语句

    /**
     * 生成批量插入语句
     *
     * @param    string $table Table name
     * @param    array $keys INSERT keys
     * @param    array $values INSERT values
     * @return    string
     */
    protected function _insertBatch($table, $keys, $values) {
        return 'INSERT INTO ' . $table . ' (' . implode(', ', $keys) . ') VALUES ' . implode(', ', $values);
    }

    #endregion

    #region 设置批量插入

    /**
     * 采用key/value为集合设置插入
     * The "set_insert_batch" function.  Allows key/value pairs to be set for batch inserts
     *
     * @param    mixed
     * @param    string
     * @param    bool
     * @return    QueryBuilder
     */
    public function setInsertBatch($key, $value = '', $escape = NULL) {
        $key = $this->_objectToArrayBatch($key);

        if (!is_array($key)) {
            $key = array($key => $value);
        }

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        $keys = array_keys($this->_objectToArray(current($key)));
        sort($keys);

        foreach ($key as $row) {
            $row = $this->_objectToArray($row);
            if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0) {
                // batch function above returns an error on an empty array
                $this->_set[] = array();
                return;
            }

            ksort($row); // puts $row in the same order as our keys

            if ($escape !== FALSE) {
                $clean = array();
                foreach ($row as $value) {
                    $clean[] = $this->escape($value);
                }

                $row = $clean;
            }

            $this->_set[] = '(' . implode(',', $row) . ')';
        }

        foreach ($keys as $k) {
            $this->_keys[] = $this->_protectIdentifiers($k, FALSE, $escape);
        }

        return $this;
    }

    #endregion

    #region UPDATE

    /**
     * UPDATE
     *
     * Compiles an update string and runs the query.
     *
     * @param    string $table
     * @param    array $set An associative array of update values
     * @param    mixed $where
     * @param    int $limit
     * @return    object
     */
    public function updateAsSet($table = '', $set = NULL, $where = NULL, $limit = NULL) {
        // Combine any cached components with the current statements
        $this->_mergeCache();

        if ($set !== NULL) {
            $this->set($set);
        }

        if ($this->_validateUpdate($table) === FALSE) {
            return FALSE;
        }

        if ($where !== NULL) {
            $this->where($where);
        }

        if (!empty($limit)) {
            $this->limit($limit);
        }

        $table = $this->_protectIdentifiers($this->_from[0], TRUE, NULL, FALSE);
        $sql = $this->_update($table, $this->_set);
        $this->_resetWrite();
        return $this->execute($sql);
    }


    #endregion

    #region 生成Update语句

    /**
     * 生成Update语句
     *
     * @access    public
     * @param    string    the table name
     * @param    array    the update data
     * @param    array    the order by clause
     * @param    array | bool the limit clause
     * @return    string
     */
    protected function _update($table, $values, $orderBy = null, $limit = FALSE) {
        foreach ($values as $key => $val) {
            if ($val !== null) {
                $valStr[] = $key . " = " . $val;
            } else {
                $valStr[] = $key;
            }
        }

        $limit = (!$limit) ? '' : ' LIMIT ' . $limit;

        $orderBy = ($orderBy && count($orderBy) >= 1) ? ' ORDER BY ' . implode(", ", $orderBy) : '';

        $sql = "UPDATE " . $table . " SET " . implode(', ', $valStr);

        $sql .= $this->_compileWhere("_where");

        $sql .= $orderBy . $limit;

        return $sql;
    }

    #endregion

    #region 验证更新

    /**
     * Validate Update
     *
     * This method is used by both update() and get_compiled_update() to
     * validate that data is actually being set and that a table has been
     * chosen to be update.
     *
     * @param    string    the table to update data on
     * @return    bool
     * @throws \Exception
     */
    protected function _validateUpdate($table = '') {
        if (count($this->_set) === 0) {
            throw new \Exception("db_must_use_index");
        }

        if ($table !== '') {
            $this->_from[0] = $table;
        } elseif (!isset($this->_from[0])) {
            throw new \Exception("db_must_set_table");
        }

        return TRUE;
    }

    #endregion

    #region 批量更新

    /**
     * 批量更新
     *
     * @param    string    要批量更新的表名
     * @param    array    更新的数组
     * @param    string    查询条件关键字
     * @return    int | bool 成功插入数据的总数 或者 插入失败返回FALSE
     * @throws \Exception
     */
    public function updateBatch($table = '', $set = NULL, $index = NULL) {
        // Combine any cached components with the current statements
        $this->_mergeCache();

        if ($index === NULL) {
            throw new \Exception("db_must_use_index");
        }

        if ($set !== NULL) {
            $this->setUpdateBatch($set, $index);
        }

        if (count($this->_set) === 0) {
            throw new \Exception("db_must_use_set");
        }

        if ($table === '') {
            if (!isset($this->_from[0])) {
                throw new \Exception("db_must_set_table");
            }

            $table = $this->_from[0];
        }

        // Batch this baby
        $affectedRows = 0;
        for ($i = 0, $total = count($this->_set); $i < $total; $i += 100) {
            $this->execute($this->_updateBatch($this->_protectIdentifiers($table, TRUE, NULL, FALSE), array_slice($this->_set, $i, 100), $this->_protectIdentifiers($index)));
            $affectedRows += $this->affectedRows();
            $this->_where = array();
        }

        $this->_resetWrite();
        return $affectedRows;
    }

    #endregion

    #region 生成批量更新语句

    /**
     * 生成批量更新语句
     *
     * @param    string $table Table name
     * @param    array $values Update data
     * @param    string $index WHERE key
     * @return    string
     */
    protected function _updateBatch($table, $values, $index) {
        $ids = array();
        foreach ($values as $key => $val) {
            $ids[] = $val[$index];

            foreach (array_keys($val) as $field) {
                if ($field !== $index) {
                    $final[$field][] = 'WHEN ' . $index . ' = ' . $val[$index] . ' THEN ' . $val[$field];
                }
            }
        }

        $cases = '';
        foreach ($final as $k => $v) {
            $cases .= $k . " = CASE \n"
                . implode("\n", $v) . "\n"
                . 'ELSE ' . $k . ' END, ';
        }

        $this->where($index . ' IN(' . implode(',', $ids) . ')', NULL, FALSE);

        return 'UPDATE ' . $table . ' SET ' . substr($cases, 0, -2) . $this->_compileWhere('_where');
    }

    #endregion

    #region 批量更新数据

    /**
     * 批量处理更新数据
     *
     * @param    array
     * @param    string
     * @param    bool
     * @return    QueryBuilder
     * @throws \Exception
     */
    public function setUpdateBatch($key, $index = '', $escape = NULL) {
        $key = $this->_objectToArrayBatch($key);

        if (!is_array($key)) {
            // @todo error
        }

        is_bool($escape) OR $escape = $this->_protectIdentifiers;

        foreach ($key as $k => $v) {
            $indexSet = FALSE;
            $clean = array();
            foreach ($v as $k2 => $v2) {
                if ($k2 === $index) {
                    $indexSet = TRUE;
                }

                $clean[$this->_protectIdentifiers($k2, FALSE, $escape)] = ($escape === FALSE) ? $v2 : $this->escape($v2);
            }

            if ($indexSet === FALSE) {
                throw new \Exception("db_batch_missing_index");
            }

            $this->_set[] = $clean;
        }

        return $this;
    }

    #endregion

    #region 基于boolean和null类型进行转义

    /**
     * 基于boolean和null类型进行转义
     *
     * @access    public
     * @param    string
     * @return    mixed
     */
    public function escape($str) {
        if (is_string($str)) {
            $str = "'" . $this->escapeStr($str) . "'";
        } elseif (is_bool($str)) {
            $str = ($str === FALSE) ? 0 : 1;
        } elseif (is_null($str)) {
            $str = 'NULL';
        }

        return $str;
    }

    #endregion

    #region 转义字符串

    /**
     * 转义字符串
     *
     * @access    public
     * @param    string
     * @param    bool    whether or not the string will be used in a LIKE condition
     * @return    string
     */
    function escapeStr($str, $like = FALSE) {
        if (is_array($str)) {
            foreach ($str as $key => $val) {
                $str[$key] = $this->escapeStr($val, $like);
            }

            return $str;
        }

        if (function_exists('mysqli_real_escape_string') AND is_object($this->coonId)) {
            $str = mysqli_real_escape_string($this->coonId, $str);
        } else {
            $str = addslashes($str);
        }

        // escape LIKE condition wildcards
        if ($like === TRUE) {
            $str = str_replace(array('%', '_'), array('\\%', '\\_'), $str);
        }

        return $str;
    }

    #endregion

    #region 获取分组类型

    /**
     * 获取分组类型
     *
     * @used-by    group_start()
     * @used-by    _like()
     * @used-by    _wh()
     * @used-by    _where_in()
     *
     * @param    string $type
     * @return    string
     */
    protected function _groupGetType($type) {
        if ($this->_whereGroupStarted) {
            $type = '';
            $this->_whereGroupStarted = FALSE;
        }

        return $type;
    }

    #endregion

    #region 对象转成数组

    /**
     * 对象转成数组
     *
     * @param    $object
     * @return    array
     */
    protected function _objectToArray($object) {
        if (!is_object($object)) {
            return $object;
        }

        $array = array();
        foreach (get_object_vars($object) as $key => $val) {
            // There are some built in keys we need to ignore for this conversion
            if (!is_object($val) && !is_array($val) && $key !== '_parent_name') {
                $array[$key] = $val;
            }
        }

        return $array;
    }

    #endregion

    #region 批量对象转成数组

    /**
     * Object to Array
     *
     * Takes an object as input and converts the class variables to array key/vals
     *
     * @param    object
     * @return    array
     */
    protected function _objectToArrayBatch($object) {
        if (!is_object($object)) {
            return $object;
        }

        $array = array();
        $out = get_object_vars($object);
        $fields = array_keys($out);

        foreach ($fields as $val) {
            // There are some built in keys we need to ignore for this conversion
            if ($val !== '_parent_name') {
                $i = 0;
                foreach ($out[$val] as $data) {
                    $array[$i++][$val] = $data;
                }
            }
        }

        return $array;
    }

    #endregion

    #region 是否是文字

    /**
     * 是否是文字
     *
     * @param    string $str
     * @return    bool
     */
    protected function _isLiteral($str) {
        $str = trim($str);

        if (empty($str) OR ctype_digit($str) OR (string)(float)$str === $str OR in_array(strtoupper($str), array('TRUE', 'FALSE'), TRUE)) {
            return TRUE;
        }

        static $_str;

        if (empty($_str)) {
            $_str = ($this->_escapeChar !== '"')
                ? array('"', "'") : array("'");
        }

        return in_array($str[0], $_str, TRUE);
    }


    #endregion

    #region 缓存相关

    #region 启用缓存

    /**
     * 启用缓存
     *
     * Starts QB caching
     *
     * @return    QueryBuilder
     */
    public function startCache() {
        $this->_caching = TRUE;
        return $this;
    }

    #endregion

    #region 停用缓存

    /**
     * 停用缓存
     *
     * Stops QB caching
     *
     * @return    QueryBuilder
     */
    public function stopCache() {
        $this->_caching = FALSE;
        return $this;
    }

    #endregion

    #region 清空缓存

    /**
     * 清空缓存
     *
     * @return    QueryBuilder
     */
    public function flushCache() {
        $this->_resetRun(array(
            '_cacheSelect' => array(),
            '_cacheFrom' => array(),
            '_cacheJoin' => array(),
            '_cacheWhere' => array(),
            '_cacheGroupBy' => array(),
            '_cacheHaving' => array(),
            '_cacheOrderBy' => array(),
            '_cacheSet' => array(),
            '_cacheExists' => array(),
            '_cacheNoEscape' => array()
        ));

        return $this;
    }

    #endregion

    #region 合并缓存

    /**
     * 合并缓存
     *
     * @return    void
     */
    protected function _mergeCache() {
        if (count($this->_cacheExists) === 0) {
            return;
        } elseif (in_array('select', $this->_cacheExists, TRUE)) {
            $noEscape = $this->_cacheNoEscape;
        }

        // select, from, etc.
        foreach (array_unique($this->_cacheExists) as $val) {
            $variable = 'qb_' . $val;
            $cacheVar = 'qb_cache_' . $val;
            $new = $this->$cacheVar;

            for ($i = 0, $c = count($this->$variable); $i < $c; $i++) {
                if (!in_array($this->{$variable}[$i], $new, TRUE)) {
                    $new[] = $this->{$variable}[$i];
                    if ($val === 'select') {
                        $noEscape[] = $this->_cacheNoEscape[$i];
                    }
                }
            }

            $this->$variable = $new;
            if ($val === 'select') {
                $this->_cacheNoEscape = $noEscape;
            }
        }

        // If we are "protecting identifiers" we need to examine the "from"
        // portion of the query to determine if there are any aliases
        if ($this->_protectIdentifiers === TRUE && count($this->_cacheFrom) > 0) {
            $this->_trackAliases($this->_from);
        }
    }

    #endregion

    #endregion

    #region 重设查询构造器属性的值

    /**
     * 重设查询构造器属性的值
     *
     * @return    QueryBuilder
     */
    public function resetQuery() {
        $this->_resetSelect();
        $this->_resetWrite();
        return $this;
    }

    #endregion

    #region 重设查询构造器属性的值

    /**
     * 重设查询构造器属性的值，被get()方法调用
     *
     * @return    void
     */
    protected function _resetSelect() {
        $this->_resetRun(array(
            '_select' => array(),
            '_from' => array(),
            '_join' => array(),
            '_where' => array(),
            '_groupBy' => array(),
            '_having' => array(),
            '_orderBy' => array(),
            '_aliasedTables' => array(),
            '_noEscape' => array(),
            '_distinct' => FALSE,
            '_limit' => FALSE,
            '_offset' => FALSE
        ));
    }

    #endregion

    #region 重设写SQL的值

    /**
     * 重设写SQL的值
     *
     * @return    void
     */
    protected function _resetWrite() {
        $this->_resetRun(array(
            '_set' => array(),
            '_from' => array(),
            '_join' => array(),
            '_where' => array(),
            '_orderBy' => array(),
            '_keys' => array(),
            '_limit' => FALSE
        ));
    }

    #endregion

    #region 运行重设查询构造器属性的值

    /**
     * 运行重设查询构造器属性的值
     *
     * @param    array    An array of fields to reset
     * @return    void
     */
    protected function _resetRun($resetItems) {
        foreach ($resetItems as $item => $defaultValue) {
            $this->$item = $defaultValue;
        }
    }

    #endregion

    #endregion
}